1. Field of the Invention
The present invention relates generally to electronic databases. More particularly, the present invention relates to storing and retrieving data from multidimensional databases.
2. Description of the Related Art
One general category of application software is often referred to as a database management program or simply a database application. Encompassed within this general category are database systems referred to commercially as multidimensional databases or, in technical discussions, as Online Analytical Processing (OLAP) data stores. The OLAP paradigm is described in the white paper entitled xe2x80x9cProviding OLAP (On-line Analytical Processing) to User-Analysts: An IT Mandatexe2x80x9d by E. F. Codd, S. B. Codd, and C. T. Salley published by Codd and Date, Inc., and incorporated by reference herein for all purposes.
Typically, a multidimensional database stores and organizes data in a way that better reflects how a user would want to view the data than is possible in a spreadsheet or relational database file. Multidimensional databases are better suited generally to handle applications with large volumes of numeric data and that require calculations on numeric data, such as business analysis and forecasting.
A dimension within multidimensional data is typically a basic categorical definition of data in a database outline (discussed in greater detail below). A multidimensional database can contain several dimensions thereby allowing analysis of a large volume of data from multiple viewpoints or perspectives. Thus, a dimension can also be described as a perspective or view of a specific dataset. A different view of the same data is referred to as an alternative dimension. A data management system that supports simultaneous, alternative views of datasets is said to be multidimensional. Using a business application as an example, dimensions are items such as TIME, ACCOUNTS, PRODUCT LINES, MARKETS, DIVISIONS, and so on. Within each dimension, there is typically a consolidation or other relationship between items.
Information in a database can be stored and maintained in various data structures. To facilitate discussion, FIG. 1A is a simplified representation of an exemplary database table 100. Data table 100 can be used to store information relating to different products that are available for sale in a department store. As shown in FIG. 1A, data table 100 can include a product ID, a product category, a product sub-category, and a product level.
Each product category, e.g., xe2x80x9cclothingxe2x80x9d, xe2x80x9cmen""s clothingxe2x80x9d, etc. can be assigned a unique product ID. Information relating to a particular product category can be accessed using its product ID. For example, product ID xe2x80x9c2xe2x80x9d in table 100 uniquely identifies xe2x80x9cinformal wearxe2x80x9d. Thus, by looking up product ID xe2x80x9c2xe2x80x9d in table 100, other information such as the fact that product categories xe2x80x9cjeansxe2x80x9d and xe2x80x9ct-shirtsxe2x80x9d are sub-categories of xe2x80x9cinformal wearxe2x80x9d can be ascertained.
Other information relating to product categories can be maintained in another table, sales table 110 of FIG. 1B. As shown in FIG. 1B, the sales table 110 includes specific information relating to sale of products, e.g., xe2x80x9cretail IDxe2x80x9d, xe2x80x9ccurrent pricexe2x80x9d, and xe2x80x9cnumber sold this year.xe2x80x9d For example, Information related to the xe2x80x9ccurrent pricexe2x80x9d for xe2x80x9cJeansxe2x80x9d can be obtained by accessing the table 110 using xe2x80x9c3xe2x80x9d as the product ID and looking up the appropriate column, i.e., xe2x80x9ccurrent pricexe2x80x9d.
FIG. 1C depicts another informational aspect (dimension) related to a department store. Namely, the organizational arrangement (xe2x80x9ctopologyxe2x80x9d) of the department store. Similar to product table 100, each particular region shown in FIG. 1C, e.g., xe2x80x9cU.S.xe2x80x9d, xe2x80x9cWest Coastxe2x80x9d can be assigned a unique regional ID. Information relating to topology of a particular region can be accessed using the regional ID that uniquely identifies that region.
As is known in the art, the Information maintained in a database, e.g., information in data tables shown in FIGS. 1A-1C, can be used to solve various analytical problems. By way of example, the department store can use the information kept in its database to solve problems of having to keep track of inventory, sales, employee records and salaries, and so on. In order to address an analytical problem, it is often necessary to combine information maintained in several database tables. In addition, an analytical problem may be related to one or more dimensions of data. By way of example, the user may wish to now the 3-rd quarter sales for a product category and all its sub-categories (e.g., xe2x80x9cinformal wearxe2x80x9d with subcategories of xe2x80x9cjeansxe2x80x9d and xe2x80x9cT-shirtsxe2x80x9d) for a regional store and its sub-regions (e.g., San Diego with its sub-region of La Jolla). This problem involves at least two dimensions related to the general category ofxe2x80x9csalesxe2x80x9d, namely, the dimensions of xe2x80x9cproductsxe2x80x9d and xe2x80x9ctopologyxe2x80x9d. Thus, in that example, the information in tables shown in FIGS. 1A-C need to be combined (merged) to adequately generate the desired sale reports.
Moreover, it is useful to have the solution organized and presented in a way that better reflects how a human would want to view the data. To elaborate, there is an implicit hierarchical (parent-child) relationship between the product categories of database table 100. For example, xe2x80x9cinformal wearxe2x80x9d is a child of xe2x80x9cclothingxe2x80x9d and a parent of both xe2x80x9cjeansxe2x80x9d and xe2x80x9cT-shirtsxe2x80x9d. The hierarchical relationship present in table 100 is illustrated in FIG. 2A, where, for example, xe2x80x9cInformal wearxe2x80x9d is represented as a child of xe2x80x9cmen""s clothingxe2x80x9d and as a parent of both xe2x80x9cjeansxe2x80x9d and xe2x80x9cT-shirtsxe2x80x9d. The graphical presentation illustrated in FIG. 2A is better suited for human perception and comprehension. This is evident from a quick comparison of FIG. 1A to FIG. 2A. As another example, FIG. 2B illustrates the hierarchical relationship present in FIG. 1C. Again, the graphical representation of FIG. 2B is a more desirable presentation to a user.
As mentioned earlier, the multidimensional databases have the ability to present a user with several different views (dimensions) of data. To facilitate understanding, a multidimensional solution provided by a multidimensional database can be represented by a multidimensional structure, e.g., a cube 120 of FIG. 2C wherein each side of the cube 120 can present the user with a different dimension of data. For example, sides 122 and 124 of the cube 120 can contain the hierarchical relationships depicted in FIGS. 2A and 2B respectively.
Recently, there have been significant developments in the area of multidimensional databases. However, primarily xe2x80x9cbrute forcexe2x80x9d approaches have been used to generate multidimensional outputs without much regard to overall cost and efficiency. As is known in the field, brute force approaches generally require making several passes through relevant tables in a database, of which there maybe many, to ultimately generate an appropriate multidimensional output.
By way of example, in order to generate a multidimensional output that adequately addresses the problem of determining the 3-rd quarter sales for a product category and its sub-categories for a regional store and its sub-regions, several passes through tables shown in FIGS. 1A-1C would have to be made. Consequently, brute force approaches are inefficient and expensive with respect to system resources. In addition, since making several passes through a database table requires significant amount of time, brute force approaches are not suitable for multidimensional databases where it is desirable to quickly present the user with several dimensions of data on demand (xe2x80x9con the flyxe2x80x9d).
Another approach for generating multidimensional outputs is to xe2x80x9cnormalizexe2x80x9d data. Normalization of data generally puts data in a format that is more readily suitable for generation of multidimensional output. Database table 130 of FIG. 2D illustrates how the hierarchical relationship present in table 1A (also illustrated in the tree representation of FIG. 2A) may be normalized. As illustrated in FIG. 2D parent-child relationships between product categories can be maintained in a format that is more suitable for generation of multidimensional outputs. For example, row 132 contains the hierarchical relationship between xe2x80x9cclothingxe2x80x9d, xe2x80x9cmen""s clothingxe2x80x9d, xe2x80x9cinformal wearxe2x80x9d, and xe2x80x9cjeansxe2x80x9d.
Although normalized data is more suitable for generation of multidimensional outputs, normalization of data has many disadvantages and limitations. One disadvantage is that generation of normalized data requires significant amount of time and resources. Thus, normalization of data is an inefficient way to generate multidimensional outputs. More specifically, static allocation of normalized tables imposes an unnecessary waste of resources and does not easily allow dynamic build or modification of the hierarchical relationships.
In view of the foregoing there is a need for improved methods for storing and retrieving data from multidimensional databases.
Broadly speaking, the invention relates to methods, apparatus, and data structures suitable for storing and retrieving data from multidimensional databases. In one aspect, the invention pertains to generation of multidimensional output suitable for presenting several aspects (dimensions) of a problem related a multidimensional database. The information used to solve the analytical problem is typically maintained in a source database. The invention can be implemented in numerous ways, including as a device, an apparatus and a method. A few embodiments of the invention are discussed below.
In accordance with one embodiment of the present invention, a multidimensional integration system suitable for accessing a source database is disclosed. The multidimensional integration system includes a multidimensional data integrator that accesses a source database to generate instructions necessary to produce multidimensional output. The multidimensional data integrator can use an Application Program Interface (API) to communicate with a multidimensional server that ultimately produces the multidimensional output.
In accordance with another embodiment of the present invention, a method for producing multidimensional output from a source database is disclosed. The multidimensional output provides a solution to a problem relating to one or more dimensions of data associated with the source database. An application relating to one or more dimensions of data is defined. The dimensions of data are associated with the source database. In addition, a Meta-model for the application is defined. The Meta-model relates to the one or more dimensions of data associated with the source database. Further, a Meta-outline is created for the application. The Meta-outline includes at least one dimension of data. Furthermore, multidimensional output is generated based on at least one hierarchical relationship defined for the source database and relating to the application. The multidimensional output provides a solution to the problem related to the application.
This invention has numerous advantages. One advantage is that multidimensional output can be generated more quickly and efficiently in a manner that is more suitable for multidimensional database environments. Another advantage is that this invention does not require normalization of data prior to creation of multidimensional output. Thus, a source database can remain in its original relational format. Still another advantage of the invention is that it is cost effective and relatively simple implementations are possible.
Other aspects and advantages of the invention will become apparent from the following detailed description, taken in conjunction with the accompanying drawings, illustrating by way of example the principles of the invention.